"""
SQL查询配置模块

此模块包含所有用于数据对比的SQL查询语句

建议的索引：
- idx_del_flag (del_flag) - 基础条件索引
- idx_level (del_flag, level) - 职级查询索引
- idx_birthday (del_flag, birthday) - 年龄计算索引
"""

# 基础字段选择
BASE_PERSONNEL_FIELDS = """
    id,
    name,
    level,
    gender,
    per_code as department,
    manage_name as position,
    DATE_FORMAT(join_work_date, '%Y-%m-%d') as entry_date,
    status
"""

# 基础表和条件
BASE_TABLE_CONDITION = """
FROM per_main 
WHERE del_flag = 0
"""

# 人员主表查询 - 基础查询
PERSONNEL_MAIN_SQL = f"""
SELECT 
    {BASE_PERSONNEL_FIELDS}
{BASE_TABLE_CONDITION}
"""

# 总人数查询
TOTAL_NUMBER_OF_PEOPLE = f"""
SELECT COUNT(*) as total 
{BASE_TABLE_CONDITION}
"""

# 职级分布查询 - 使用 idx_level 索引
RANK_OF_RANK = f"""
SELECT 
    level,
    COUNT(*) as count
{BASE_TABLE_CONDITION}
  AND level IS NOT NULL 
GROUP BY level
ORDER BY level
"""

# 平均年龄查询 - 使用 idx_birthday 索引
AVERAGE_AGE_SQL = f"""
SELECT 
    AVG(
        TIMESTAMPDIFF(
            YEAR,  
            STR_TO_DATE(birthday, '%Y-%m-%d'), 
            CURRENT_DATE
        )
    ) as avg_age
{BASE_TABLE_CONDITION}
  AND birthday IS NOT NULL
"""

# 按职级查询人员 - 使用 idx_level 索引
PERSONNEL_BY_LEVEL_SQL = f"""
SELECT 
    {BASE_PERSONNEL_FIELDS}
{BASE_TABLE_CONDITION}
  AND level = :level
ORDER BY id
"""

# 获取所有人员数据
GET_ALL_PERSONNEL_SQL = f"""
SELECT 
    {BASE_PERSONNEL_FIELDS}
{BASE_TABLE_CONDITION}
ORDER BY id
"""

# 按ID查询单个人员
GET_PERSONNEL_BY_ID = f"""
SELECT 
    {BASE_PERSONNEL_FIELDS}
{BASE_TABLE_CONDITION}
  AND id = :id
"""

# 更新人员信息 - 按字段
UPDATE_NAME_SQL = """
UPDATE per_main SET name = :value WHERE id = :id
"""

UPDATE_LEVEL_SQL = """
UPDATE per_main SET level = :value WHERE id = :id
"""

UPDATE_GENDER_SQL = """
UPDATE per_main SET gender = :value WHERE id = :id
"""

UPDATE_DEPARTMENT_SQL = """
UPDATE per_main SET per_code = :value WHERE id = :id
"""

UPDATE_POSITION_SQL = """
UPDATE per_main SET manage_name = :value WHERE id = :id
"""

UPDATE_ENTRY_DATE_SQL = """
UPDATE per_main SET join_work_date = :value WHERE id = :id
"""

UPDATE_STATUS_SQL = """
UPDATE per_main SET status = :value WHERE id = :id
"""

# 字段映射
UPDATE_SQL_MAPPING = {
    'name': UPDATE_NAME_SQL,
    'level': UPDATE_LEVEL_SQL,
    'gender': UPDATE_GENDER_SQL,
    'department': UPDATE_DEPARTMENT_SQL,
    'position': UPDATE_POSITION_SQL,
    'entry_date': UPDATE_ENTRY_DATE_SQL,
    'status': UPDATE_STATUS_SQL
}

# 删除人员（软删除）
SOFT_DELETE_PERSONNEL_SQL = """
UPDATE per_main 
SET del_flag = 1 
WHERE id = :id
"""

# 新增人员
INSERT_PERSONNEL_SQL = """
INSERT INTO per_main (
    name, level, gender, per_code, manage_name, 
    join_work_date, status, del_flag
) VALUES (
    :name, :level, :gender, :department, :position, 
    :entry_date, :status, 0
)
"""

# 登录尝试相关的 SQL 语句
LOGIN_ATTEMPTS_SQL = {
    'create_table': '''
        CREATE TABLE IF NOT EXISTS login_attempts (
            ip_address VARCHAR(45) PRIMARY KEY,
            attempts INT DEFAULT 0,
            last_attempt DATETIME,
            first_attempt DATETIME,
            INDEX idx_first_attempt (first_attempt)
        )
    ''',
    
    'get_attempts': '''
        SELECT attempts, last_attempt, first_attempt 
        FROM login_attempts 
        WHERE ip_address = :ip
    ''',
    
    'get_current_attempts': '''
        SELECT attempts 
        FROM login_attempts 
        WHERE ip_address = :ip
    ''',
    
    'update_attempts': '''
        UPDATE login_attempts 
        SET attempts = :attempts, last_attempt = :last_attempt 
        WHERE ip_address = :ip
    ''',
    
    'insert_attempt': '''
        INSERT INTO login_attempts 
        (ip_address, attempts, last_attempt, first_attempt)
        VALUES (:ip, 1, :time, :time)
    ''',
    
    'reset_attempts': '''
        UPDATE login_attempts 
        SET attempts = 0, last_attempt = :time 
        WHERE ip_address = :ip
    ''',
    
    'cleanup_attempts': '''
        DELETE FROM login_attempts 
        WHERE first_attempt < :expired_time 
        AND attempts < :max_attempts
    '''
}

# 按belongId查询各组织总人数
ORG_TOTAL_BY_BELONG_ID = """
SELECT count(so.code) as textValue, count(so.code) as operationValue, so.id as belongId from 
sys_org so LEFT JOIN
(SELECT
REPLACE(b.path_code,"-",",") pathCode
FROM
    per_main pm
    JOIN sys_org b ON pm.org_code = b.CODE
    ) temp0
    on FIND_IN_SET(so.`code`,temp0.pathCode)    
    GROUP BY
    so.id
"""

# 组织数据指标相关SQL
TOTAL_PEOPLE_BY_ORG = """
SELECT count(so.code) as total_count
FROM sys_org so LEFT JOIN
(SELECT
REPLACE(b.path_code,"-",",") pathCode
FROM
	per_main pm
	JOIN sys_org b ON pm.org_code = b.CODE
	) temp0
	on FIND_IN_SET(so.`code`,temp0.pathCode)	
WHERE so.id = :belong_id
GROUP BY so.id
"""

FORMAL_PEOPLE_BY_ORG = """
SELECT count(so.code) as formal_count
FROM sys_org so LEFT JOIN
(SELECT
REPLACE(b.path_code,"-",",") pathCode
FROM
	per_main pm
	JOIN sys_org b ON pm.org_code = b.CODE
	) temp0
	on FIND_IN_SET(so.`code`,temp0.pathCode)	
WHERE so.id = :belong_id
GROUP BY so.id
"""

PROBATION_PEOPLE_BY_ORG = """
SELECT count(so.code) as probation_count
FROM sys_org so LEFT JOIN
(SELECT
REPLACE(b.path_code,"-",",") pathCode
FROM
	per_main pm
	JOIN sys_org b ON pm.org_code = b.CODE
	) temp0
	on FIND_IN_SET(so.`code`,temp0.pathCode)	
WHERE so.id = :belong_id
GROUP BY so.id
"""

MANAGER_COUNT_BY_ORG = """
SELECT count(so.code) as manager_count
FROM sys_org so LEFT JOIN
(SELECT
REPLACE(b.path_code,"-",",") pathCode
FROM
	per_main pm
	JOIN sys_org b ON pm.org_code = b.CODE
	) temp0
	on FIND_IN_SET(so.`code`,temp0.pathCode)	
WHERE so.id = :belong_id
GROUP BY so.id
"""

# 男女比例SQL
GENDER_RATIO_BY_ORG = """
select belongId, concat('1:', round(if(female = 0, 0, female / male), 2)) as gender_ratio
from (
	SELECT so.id as belongId, sum(CASE WHEN gender = '1' THEN 1 ELSE 0 END) as male, sum(CASE WHEN gender = '2' THEN 1 ELSE 0 END) as female
	from sys_org so
	LEFT JOIN(
		SELECT REPLACE(b.path_code,"-",",") pathCode, pm.gender
		FROM per_main pm
		JOIN sys_org b ON pm.org_code = b.CODE
	) temp0 on FIND_IN_SET(so.`code`,temp0.pathCode)
	WHERE so.id = :belong_id
	GROUP BY so.id
) t
"""

# SQL配置字典，注册所有查询语句
SQL_CONFIG = {
    'TOTAL_PEOPLE_BY_ORG': TOTAL_PEOPLE_BY_ORG,
    'FORMAL_PEOPLE_BY_ORG': FORMAL_PEOPLE_BY_ORG,
    'PROBATION_PEOPLE_BY_ORG': PROBATION_PEOPLE_BY_ORG,
    'MANAGER_COUNT_BY_ORG': MANAGER_COUNT_BY_ORG,
    'TOTAL_NUMBER_OF_PEOPLE': TOTAL_NUMBER_OF_PEOPLE,
    'RANK_OF_RANK': RANK_OF_RANK,
    'PERSONNEL_MAIN_SQL': PERSONNEL_MAIN_SQL,
    'AVERAGE_AGE_SQL': AVERAGE_AGE_SQL,
    'PERSONNEL_BY_LEVEL_SQL': PERSONNEL_BY_LEVEL_SQL,
    'GET_ALL_PERSONNEL_SQL': GET_ALL_PERSONNEL_SQL,
    'ORG_TOTAL_BY_BELONG_ID': ORG_TOTAL_BY_BELONG_ID,
    'GENDER_RATIO_BY_ORG': GENDER_RATIO_BY_ORG
}
